package com.hkts.reportform.datastatics.daoImpl;

import com.hkts.reportform.datastatics.dao.CustomSearchDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassNameCustomFindDaoImpl
 * @Description  erp  大报表 sql查询
 * @Author yck
 * @Date2020-10-30 10:11
 * @Version V1.0
 **/
@Repository("customSearchDaoImpl")
public class CustomSearchDaoImpl implements CustomSearchDao {
    @Autowired
    private NamedParameterJdbcTemplate temple;

    @Override
    //石油焦消耗按名称分类
    public List<Map<String, Object>> cusomeFindStockByCode(String beginDate, String endDate, String cInvCodeS) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("SELECT i.cInvCode as cInvCode,i.cInvName as cInvName,ConverT(FLOAT,Round( d.iquantity,2))  as iquantity  ");
        buffer.append("FROM Inventory as i INNER JOIN ( ");
        buffer.append("SELECT rs.cInvCode AS cInvCode,SUM (isnull( rs.iQuantity, 0.000000 )) AS iquantity ");
        buffer.append("FROM rdrecord11 r INNER JOIN rdrecords11 rs ON r.ID= rs.ID  ");
        buffer.append(" WHERE isnull( cHandler, '' ) <> '' ");
        buffer.append(" and  r.ddate >=:beginDate and r.ddate <=:endDate ");
        buffer.append("AND rs.cInvCode like '"+cInvCodeS+"%' ");

        buffer.append(" GROUP BY  rs.cInvCode ");
        buffer.append(") AS d ON i.cInvCode= d.cInvCode ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    @Override
    //消耗情况 参数为二级存货类别
    public List<Map<String, Object>> cusomeFindStock(String beginDate, String endDate, String cInvCodeS) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("SELECT CONVERT(VARCHAR(100),d.dDate,23)  as dDate,i.cInvCode as cInvCode,i.cInvName as cInvName,ConverT(FLOAT,Round( d.iquantity,2))  as iquantity ");
        buffer.append("FROM Inventory as i INNER JOIN (SELECT rs.cInvCode AS cInvCode,r.dDate as dDate,SUM (isnull( rs.iQuantity, 0.000000 )) AS iquantity ");
        buffer.append("FROM rdrecord11 r INNER JOIN rdrecords11 rs ON r.ID= rs.ID ");
        buffer.append("WHERE isnull( cHandler, '' ) <> '' AND rs.cInvCode NOT LIKE '0202%' ");
        buffer.append(" and  r.ddate >=:beginDate and r.ddate <=:endDate ");
        buffer.append(" AND rs.cInvCode like '"+cInvCodeS+"%' ");
        buffer.append("GROUP BY r.dDate, rs.cInvCode ) AS d ON i.cInvCode= d.cInvCode");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    @Override
    //消耗情况 参数为二级存货类别
    public List<Map<String, Object>> cusomeFindStockUpdate(String beginDate, String endDate, String cInvCodeS) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("SELECT  ConverT(FLOAT,SUM (isnull( rs.iQuantity, 0.000000 )),2) AS iquantity ,CONVERT(VARCHAR(100),r.ddate,23)  as dDate ");
        buffer.append("FROM rdrecord11 r INNER JOIN rdrecords11 rs ON r.ID= rs.ID ");
        buffer.append("WHERE isnull( cHandler, '' ) <> ''  ");
        buffer.append(" and  r.ddate >=:beginDate and r.ddate <=:endDate ");
        buffer.append(" AND rs.cInvCode like '"+cInvCodeS+"%' ");
        buffer.append(" GROUP BY r.dDate order by r.dDate  ");

        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }
    //生产情况修改版
    @Override
    public List<Map<String, Object>> finishedProductsDataUpdate(String beginDate, String endDate, String cInvCodeS) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("SELECT i.cInvCode as cInvCode,i.cInvName as cInvName,ConverT(FLOAT,Round( d.iquantity,2))  as iquantity ");
        buffer.append("FROM Inventory as i INNER JOIN (SELECT rs.cInvCode AS cInvCode,SUM (isnull( rs.iQuantity, 0.000000 )) AS iquantity ");
        buffer.append("FROM rdrecord10 r INNER JOIN rdrecords10 rs ON r.ID= rs.ID ");
        buffer.append("WHERE isnull( cHandler, '' ) <> '' AND rs.cInvCode NOT LIKE '0202%' ");
        buffer.append(" and  r.ddate >=:beginDate and r.ddate <=:endDate ");
        buffer.append(" AND rs.cInvCode like '"+cInvCodeS+"%' ");
        buffer.append(" GROUP BY rs.cInvCode )  ");
        buffer.append("  AS d ON i.cInvCode= d.cInvCode");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }
    //生产情况按时间分组--
    @Override
    public List<Map<String, Object>> finishedProductsData(String beginDate, String endDate, String cInvCodeS) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("SELECT CONVERT(VARCHAR(100),d.dDate,23)  as dDate,i.cInvCode as cInvCode,i.cInvName as cInvName,ConverT(FLOAT,Round( d.iquantity,2))  as iquantity ");
        buffer.append("FROM Inventory as i INNER JOIN (SELECT rs.cInvCode AS cInvCode,r.dDate as dDate,SUM (isnull( rs.iQuantity, 0.000000 )) AS iquantity ");
        buffer.append("FROM rdrecord10 r INNER JOIN rdrecords10 rs ON r.ID= rs.ID ");
        buffer.append("WHERE isnull( cHandler, '' ) <> '' AND rs.cInvCode NOT LIKE '0202%' ");
        buffer.append(" and  r.ddate >=:beginDate and r.ddate <=:endDate ");
        buffer.append(" AND rs.cInvCode like '"+cInvCodeS+"%' ");
        buffer.append("GROUP BY r.dDate, rs.cInvCode ) AS d ON i.cInvCode= d.cInvCode");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    @Override
    public Float inventoryCusomeByCInvCode(String beginDate, String endDate, String cInvCodeS) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select sum(isnull(rs.iquantity,0.000000))  as iquantity ");
        buffer.append("from rdrecord11 r inner join rdrecords11 rs on rs.id=r.id ");
        buffer.append(" where isnull(r.chandler,'')<> ''  AND rs.cInvCode LIKE '"+cInvCodeS+"%' AND  r.ddate >=:beginDate and r.ddate <=:endDate ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        if(maps.size()==0){
            return (float) 0;
        }
        float iquantity = Float.parseFloat(maps.get(0).get("iquantity").toString());
        return iquantity;
    }

    @Override
    public Float inventoryeProductByCInvCode(String beginDate, String endDate, String cInvCodeS) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select sum(isnull(rs.iquantity,0.000000))  as iquantity ");
        buffer.append("from rdrecord10 r inner join rdrecords10 rs on rs.id=r.id ");
        buffer.append(" where isnull(r.chandler,'')<> ''  AND rs.cInvCode LIKE '"+cInvCodeS+"%' AND  r.ddate >=:beginDate and r.ddate <=:endDate ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        if(maps.size()==0){
            return (float) 0;
        }
        float iquantity = Float.parseFloat(maps.get(0).get("iquantity").toString());
        return iquantity;
    }
    @Override
    public List<Map<String, Object>> accessoriesSumByDate( String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("SELECT SUM(isnull( rs.iquantity, 0.000000 )) AS iquantity , CONVERT(VARCHAR(100),r.dDate,23) as DDFY ");
        buffer.append("FROM rdrecord11 r INNER JOIN rdrecords11 rs ON rs.id= r.id  ");
        buffer.append(" WHERE isnull( r.chandler, '' ) <> '' AND rs.cInvCode NOT LIKE '02%' AND rs.cInvCode NOT LIKE '01%' AND rs.cInvCode NOT LIKE '03%'");
        buffer.append(" AND  r.ddate >=:beginDate and r.ddate <=:endDate  ");
        buffer.append(" GROUP BY r.dDate ORDER BY r.dDate ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    @Override
    public List<Map<String, Object>> accessoriesTopTen(String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select ConverT(FLOAT,Round( rr.iquantity,2)) as iquantity ,i.cInvName as  cInvName from ( ");
        buffer.append("SELECT  SUM(isnull( rs.iquantity, 0.000000 )) AS iquantity , rs.cInvCode as cInvCode  ");
        buffer.append("FROM rdrecord11 r INNER JOIN rdrecords11 rs ON rs.id= r.id ");
        buffer.append(" WHERE isnull( r.chandler, '' ) <> '' AND rs.cInvCode NOT LIKE '02%' AND rs.cInvCode NOT LIKE '01%' AND rs.cInvCode NOT LIKE  '03%' ");
        buffer.append(" AND  r.ddate >=:beginDate and r.ddate <=:endDate ");
        buffer.append(" GROUP BY rs.cInvCode ) rr inner join Inventory  i on rr.cInvCode=i.cInvCode ORDER BY rr.iquantity DESC ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps.subList(0,10);
    }

    @Override
    public List<Map<String, Object>> typesOfAccessories(String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select cc.cInvCName as name,dd.iquantity as data from  InventoryClass cc left join(  ");
        buffer.append("select sum (ss.iquantity) as iquantity,ss.cInvCode as cInvCode ");
        buffer.append("from ( ");
        buffer.append("select rr.iquantity as iquantity,LEFT(i.cInvCode,2) as cInvCode from ( ");
        buffer.append(" SELECT SUM(isnull( rs.iquantity, 0.000000 )) AS iquantity , rs.cInvCode as cInvCode  ");
        buffer.append(" FROM rdrecord11 r INNER JOIN rdrecords11 rs ON rs.id= r.id ");
        buffer.append(" WHERE isnull( r.chandler, '' ) <> '' AND rs.cInvCode NOT LIKE '02%' AND rs.cInvCode NOT LIKE '01%' AND rs.cInvCode NOT LIKE '03%'  ");
        buffer.append(" AND  r.ddate >=:beginDate and r.ddate <=:endDate ");
        buffer.append(" GROUP BY rs.cInvCode ) rr inner join Inventory  i on rr.cInvCode=i.cInvCode ) ss group by ss.cInvCode) dd  ");
        buffer.append(" on cc.cInvCCode =dd.cInvCode  where cc.iInvCGrade=1 and cInvCCode!=01 and cInvCCode!=02 and cInvCCode!=03 ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }
}
